package fact.view.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.sql.DataSource;

import com.sun.faces.util.Util;
import com.sun.org.apache.xml.internal.serializer.utils.Utils;

import fact.view.DTO.ReportDTO;
import fact.view.util.AppUtil;


@ManagedBean(name = "factReportModelBean")
@RequestScoped
public class FactReportModelBean {

	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	
	
	public ArrayList<ReportDTO> getFactReport(String userId,String monthYearStr) throws SQLException
	{
		AppUtil util=new AppUtil();
		ArrayList<ReportDTO> factList=new ArrayList<ReportDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement	ps = con.prepareStatement("SELECT    x.insert_month,x.insert_year, " +
				" MAX(DECODE(x.fieldid, 1, x.fieldValue)) ACTUAL " +
				" ,MAX(DECODE(x.fieldid, 2, x.fieldValue)) TARGET " +
				" ,MAX(DECODE(x.fieldid, 3, x.fieldValue)) MARGIN " +
				" FROM	( " +
				"   Select fieldid,fieldvalue,field_name,insert_month,insert_year from DTL_FACT,MST_FACT  " +
				"   where  (insert_month,insert_year) in ("+monthYearStr+")  and" +
				"   dtl_fact.FIELDID=mst_fact.FIELD_ID  and userid=?  order by insert_month,insert_year,fieldid) x " +
				"   GROUP BY x.insert_month,x.insert_year            " +
				"   order by x.insert_month,x.insert_year");
		
		
				ps.setString(1, userId);
				ResultSet result =  ps.executeQuery();
				
				while(result.next())
				{
					ReportDTO rdto=new ReportDTO();
					rdto.setMonthYear(util.getMonth(Integer.valueOf(result.getString("INSERT_MONTH")))+", "+result.getString("INSERT_YEAR"));
					rdto.setTotal(result.getInt("ACTUAL"));
					rdto.setActual(result.getInt("ACTUAL"));
					rdto.setTarget(result.getInt("TARGET"));
					rdto.setMargin(result.getInt("MARGIN"));					
					factList.add(rdto);
				
				}
			
			
	
		
		return factList;
	}
	
	public String createMonthYearStr(int fromMonth,int fromYear, int toMonth, int toYear)
	{
		
		String str="";
		for(int i=fromYear;i<=toYear;i++)
		{
			if(i==toYear)
			{
				for(int j=fromMonth;j<=toMonth;j++)
				{
					str+="("+j+","+i+")"+",";
				}
			}
			else
			{
				for(int j=fromMonth;j<=11;j++)
				{
					str+="("+j+","+i+")"+",";
				}
			}
		}
		
		if(str.length()>0)
			str=str.substring(0, str.length()-1);
		
		return str;
	}

	
}
